import pandas as pd
import numpy as np
from sklearn.metrics import accuracy_score, mean_squared_error, r2_score, mean_squared_log_error, mean_absolute_error
from sklearn.preprocessing import StandardScaler,MinMaxScaler
from scipy.spatial import distance
from sklearn.tree import DecisionTreeRegressor
from sklearn.linear_model import LinearRegression, SGDRegressor
from sklearn.ensemble import RandomForestRegressor, GradientBoostingRegressor, AdaBoostRegressor, BaggingRegressor, ExtraTreesRegressor
import sklearn.model_selection as ms
from sklearn.neighbors import KNeighborsRegressor
import xgboost as xgb
from sklearn.linear_model import Lasso, Ridge, ElasticNet
import datetime
start = datetime.datetime.now()
gamma_list = [1.5, 3]
neighbors_min = 3
neighbors_max = 30
guiyihua_list = ['MinMaxScaler','StandardScaler']
modelname_list = ['Linear', 'XGB', 'RandomForest', 'GradientBoosting', 'AdaBoost', 'Bagging', 'DecisionTree', 'ExtraTrees', 'Lasso', 'Ridge', 'ElasticNet']
# , 'KNeighbors'
gamma = 1.5
guiyihua = 'StandardScaler'
neighbors_num = 4
modelname = 'Linear'

bf_no = '1'
xlsx_name = 'D:/repos/sicost/fe_s_' + str(bf_no) +'.xlsx'
df0 = pd.read_excel(xlsx_name)
df0['PROD_DATE'] = df0['PROD_DATE'].astype(str)

today = datetime.date.today()
if today.month == 1:
    last_month_year = today.year - 1
    last_month = 12
else:
    last_month_year = today.year
    last_month = today.month
last_day_of_last_month = datetime.date(last_month_year, last_month, 1) - datetime.timedelta(days=1)
p_day_1 = last_day_of_last_month.strftime("%Y%m%d")
p_day_2 = p_day_1[0:6] + '01'
df0_train = df0[(df0['PROD_DATE'] < p_day_1)]
df0_train = df0_train.reset_index(drop=True)
# df0_test = df0[(df0['PROD_DATE'] < p_day_1) & (df0['PROD_DATE'] >= p_day_2)]
# df0_test = df0_test.reset_index(drop=True)
# df0_train.drop(['PROD_DATE'], axis=1, inplace=True)
df0_train.drop(['UNIT_NO'], axis=1, inplace=True)
# df0_test.drop(['PROD_DATE'], axis=1, inplace=True)
# df0_test.drop(['UNIT_NO'], axis=1, inplace=True)
df_model_score = pd.DataFrame(columns=['gamma', 'neighbors_num', 'guiyihua', 'modelname', 'mse', 'mae', 'rmse', 'r2'])
dict_score = {}
def clean_data(df, gamma):
    column_name_list = df.columns.tolist()
    column_name_list.remove('PROD_DATE')
    column_name_num = len(column_name_list)
    clean_str_start = 'df_new = df['
    clean_str_end = ']'
    ldict1 = {}
    for i in range(0, column_name_num):
        print(i)
        print(column_name_list[i])
        column_name_tmp = column_name_list[i]
        exec("q1_{} = df['{}'].quantile(0.25)".format(i, column_name_tmp), locals(), ldict1)
        exec("q3_{} = df['{}'].quantile(0.75)".format(i, column_name_tmp), locals(), ldict1)
        exec("iqr_val_{} = q3_{} - q1_{}".format(i, i, i), locals(), ldict1)
        # clean_str1 = "(df0['SUM_CACULATE_IRON_WGT'] >= q1_0 - gamma * iqr_val_0)"
        exec('''clean_str1 = "(df['{}'] >= ldict1['q1_{}'] - gamma * ldict1['iqr_val_{}'])"'''.format(column_name_tmp, i, i), locals(), ldict1)
        exec('''clean_str2 = "(df['{}'] < ldict1['q3_{}'] + gamma * ldict1['iqr_val_{}'])"'''.format(column_name_tmp, i, i), locals(), ldict1)
        clean_str1 = ldict1["clean_str1"]
        clean_str2 = ldict1["clean_str2"]
        if i == 0:
            clean_str_start = clean_str_start + clean_str1 + ' & ' + clean_str2
        else:
            clean_str_start = clean_str_start + ' & ' + clean_str1 + ' & ' + clean_str2
    clean_str = clean_str_start + clean_str_end
    print(clean_str)
    exec(clean_str, locals(), ldict1)
    df_new = ldict1["df_new"]
    df_new = df_new.reset_index(drop=True)
    return df_new
for gamma in gamma_list:
    df0_train_new = clean_data(df0_train, gamma)
    df0_test = df0_train_new[(df0_train_new['PROD_DATE'] < p_day_1) & (df0_train_new['PROD_DATE'] >= p_day_2)]
    df0_test_copy = df0_test.copy()
    df0_test_copy = df0_test_copy.reset_index(drop=False)
    df0_test_copy.rename(columns={'index': 'index_old'}, inplace=True)
    df0_test_merge = df0_test_copy[['index_old', 'AVG_S_VALUE']]
    df0_train_new.drop(['PROD_DATE'], axis=1, inplace=True)
    df0_train_new.drop(['SUM_CACULATE_IRON_WGT'], axis=1, inplace=True)
    df0_train_new_X = df0_train_new.drop(labels=['AVG_S_VALUE'], axis=1, inplace=False)
    for guiyihua in guiyihua_list:
        if guiyihua == 'StandardScaler':
            transfer = StandardScaler()
        else:
            transfer = MinMaxScaler()
        X_trans = transfer.fit_transform(df0_train_new_X)
        df0_X_trans = pd.DataFrame(X_trans)
        df0_X_trans_copy = df0_X_trans.copy()
        df0_X_trans_copy = df0_X_trans_copy.reset_index(drop=False)
        df0_X_trans_copy.rename(columns={'index': 'index_old'}, inplace=True)
        v = ['index_old']
        df_merge = pd.merge(df0_test_merge, df0_X_trans_copy, on=v, how='left')
        # df_merge.drop(['index_old'], axis=1, inplace=True)
        test_index_list = df_merge['index_old'].tolist()
        # for i in range(neighbors_min, neighbors_max):
        #     print(i)
        #     neighbors_num = i
        for modelname in modelname_list:
            print(modelname)

            y_pred_list = []
            for test_index_tmp in test_index_list:
                print(test_index_tmp)
                df0_X_trans_copy2 = df0_X_trans.copy()
                input_data_X = df0_X_trans_copy2.iloc[test_index_tmp].tolist()
                input_X = df0_X_trans_copy2.iloc[-1:]
                df0_X_new = df0_X_trans_copy2
                df0_X_new['distance'] = df0_X_new.apply(lambda row: distance.euclidean(row, input_data_X), axis=1)
                df0_X_new['y'] = df0_train_new['AVG_S_VALUE']
                df_sorted = df0_X_new.sort_values(by='distance')
                df_sorted = df_sorted.reset_index(drop=True)

                df_head = df_sorted.head(neighbors_num)
                if modelname == 'Linear':
                    model = LinearRegression()
                elif modelname == 'XGB':
                    model = xgb.XGBRegressor()
                elif modelname == 'RandomForest':
                    model = RandomForestRegressor()
                elif modelname == 'GradientBoosting':
                    model = GradientBoostingRegressor()
                elif modelname == 'AdaBoost':
                    model = AdaBoostRegressor()
                elif modelname == 'Bagging':
                    model = BaggingRegressor()
                elif modelname == 'DecisionTree':
                    model = DecisionTreeRegressor()
                elif modelname == 'ExtraTrees':
                    model = ExtraTreesRegressor()
                elif modelname == 'KNeighbors':
                    model = KNeighborsRegressor()
                elif modelname == 'Lasso':
                    model = Lasso(alpha=0.1)
                elif modelname == 'Ridge':
                    model = Ridge(alpha=1.0)
                elif modelname == 'ElasticNet':
                    model = ElasticNet(alpha=1.0, l1_ratio=0.5)
                else:
                    model = LinearRegression()
                X = df_head.drop(labels=['distance', 'y'], axis=1, inplace=False).values
                y = df_head['y'].values
                X_input = input_X.values
                model.fit(X, y)
                y_pred = model.predict(X_input)
                print(y_pred)
                # print(float(y_pred))
                y_pred_list.append(float(y_pred))
            y_true_list = df_merge['AVG_S_VALUE'].tolist()
            # 计算模型的准确度
            MSE = mean_squared_error(y_true_list, y_pred_list)
            MAE = mean_absolute_error(y_true_list, y_pred_list)
            RMSE = np.sqrt(mean_squared_error(y_true_list, y_pred_list))
            # RMSLE = np.sqrt(mean_squared_log_error(y_true_list, y_pred_list))
            R2 = r2_score(y_true_list, y_pred_list)
            # print(f"Model Accuracy: {MSE}")
            dict_score['gamma'] = gamma
            dict_score['neighbors_num'] = neighbors_num
            dict_score['guiyihua'] = guiyihua
            dict_score['modelname'] = modelname
            dict_score['mse'] = MSE
            dict_score['mae'] = MAE
            dict_score['rmse'] = RMSE
            dict_score['r2'] = R2
            new_row = pd.Series(dict_score)
            df_model_score = df_model_score.append(new_row, ignore_index=True)
writer = pd.ExcelWriter('model_score_4.xlsx')
df_model_score.to_excel(writer, sheet_name='Sheet1', index=False)
writer.save()
elapsed = float((datetime.datetime.now() - start).seconds)
print("Time Used 4 All ----->>>> %f seconds" % (elapsed))
print('finish')